﻿/*
 * 作者：唐桥
 * 功能描述：实现Sql Server数据库的中添加和删除数据
 * 时间：2018-1-17
 * 修改人：唐桥
 * 修改时间：2018-1-18 22:10
 *
 */


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.OleDb;
using DataAccess;
using UI.BasicData;
using System.Data.SqlClient;
using DataBusiness;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using Entity;

namespace UI
{
    public partial class frmLithologicSection : DevExpress.XtraEditors.XtraForm
    {
        //private DataTable dispDt;
        public int num=0;
        public frmLithologicSection()
        {
            InitializeComponent();
        }

        public static string GetExcelFirstTableName(string connectstring)
        {
            using (OleDbConnection connection = new OleDbConnection(connectstring))
            {
                string tableName = string.Empty;
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    tableName = ConvertTo<string>(dt.Rows[0][2]);
                }
                return tableName;
            }
        }
        public static object ConvertTo(object data, Type targetType)
        {
            if (data == null || Convert.IsDBNull(data))
            {
                return null;
            }
            Type type2 = data.GetType();
            if (targetType == type2)
            {
                return data;
            }
            if (((targetType == typeof(Guid)) || (targetType == typeof(Guid?))) && (type2 == typeof(string)))
            {
                if (string.IsNullOrEmpty(data.ToString()))
                {
                    return null;
                }
                return new Guid(data.ToString());
            }
            if (targetType.IsEnum)
            {
                try
                {
                    return Enum.Parse(targetType, data.ToString(), true);
                }
                catch
                {
                    return Enum.ToObject(targetType, data);
                }
            }
            if (targetType.IsGenericType)
            {
                targetType = targetType.GetGenericArguments()[0];
            }
            return Convert.ChangeType(data, targetType);
        }

        public static T ConvertTo<T>(object data)
        {
            if (data == null || Convert.IsDBNull(data))
                return default(T);
            object obj = ConvertTo(data, typeof(T));
            if (obj == null)
            {
                return default(T);
            }
            return (T)obj;
        }


        private void btnImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            ofd.Filter = "所有文件(*.*)|*.*|Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx|Excel2013文件(*.xlsx)|*.xlsx";
            ofd.ValidateNames = true;
            ofd.CheckFileExists = true;
            ofd.CheckPathExists = true;
            string strName = string.Empty;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                strName = ofd.FileName;
            }
            if (strName == "")
            {
                XtraMessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            else
            {
                try
                {
                    DataSet myDs = new DataSet();
                    string text = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0", strName);
                    string excelFirstTableName = GetExcelFirstTableName(text);
                    myDs.Tables.Clear();
                    myDs.Clear();
                    this.gridControl1.DataSource = null;
                    OleDbConnection selectConnection = new OleDbConnection(text);
                    OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(string.Format("select * from [{0}]", excelFirstTableName), selectConnection);
                    oleDbDataAdapter.Fill(myDs);
                    this.gridControl1.DataSource = myDs.Tables[0];
                    this.gridView1.PopulateColumns();
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show("从电子表格文件中装载数据异常！", ex.Message);
                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            frmLithologicSectionAdd newLithologic_section = new frmLithologicSectionAdd();
            newLithologic_section.Show();
            if (newLithologic_section.returnFlag == 1)
            {
                gridControl1.DataSource = LithologicSectionBLL.GetLithologicSection().Tables[0];
            }
        }

        private void frmLithologicSection_Load(object sender, EventArgs e)
        {
            gridControl1.DataSource = LithologicSectionBLL.GetLithologicSection().Tables[0];
        }
       
        /// <summary>删除岩性剖面选中的单条记录信息 </summary>
        /// <param name="">岩性剖面信息对象</param>
        /// <returns>返回所影响的数据行数</returns>
        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult dialogResult = MessageBox.Show("是否删除该记录？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            if (dialogResult == DialogResult.OK)
            {
                int taskNum = int.Parse(gridView1.GetFocusedRowCellValue("编号").ToString());
                String sSql = "delete from tb_LithologicSection where ID='" + taskNum + "'";
                LithologicSectionBLL.DeleteLithologicSection(sSql);
                gridView1.DeleteRow(gridView1.FocusedRowHandle);
            }
        }
        private void btnSave_Click(object sender, EventArgs e)
        {
            
            string colValue0 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[0]).ToString();
            string colValue1 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[1]).ToString();
            string colValue2 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[2]).ToString();
            string colValue3 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[3]).ToString();
            string colValue4 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[4]).ToString();
            string colValue5 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[5]).ToString();
            string colValue6 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[6]).ToString();
            string colValue7 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[7]).ToString();
            string colValue8 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[8]).ToString();
            string colValue9 = this.gridView1.GetRowCellValue(this.gridView1.FocusedRowHandle, this.gridView1.Columns[9]).ToString();
            tb_LithologicSectionModel t = new tb_LithologicSectionModel(Convert.ToInt32(colValue0), colValue1, Convert.ToInt32(colValue2), colValue3, Convert.ToDecimal(colValue4), Convert.ToDecimal(colValue5), Convert.ToDecimal(colValue6), Convert.ToDecimal(colValue7), Guid.NewGuid(), DateTime.Now);
            //t.ID1 = Convert.ToInt32(colValue0);
            t.WellNo1= colValue1;
            t.Horizon1 = string.IsNullOrEmpty(colValue3) ? 0 : Convert.ToInt32(colValue2);
            t.MainLithology1 = colValue3;
            t.WellDepth1 = string.IsNullOrEmpty(colValue4) ? 0 : Convert.ToDecimal(colValue4);
            t.LiThickness1 = string.IsNullOrEmpty(colValue5) ? 0 : Convert.ToDecimal(colValue5);
            t.StartLocation1 = string.IsNullOrEmpty(colValue6) ? 0 : Convert.ToDecimal(colValue6);
            t.EndLocation1 = string.IsNullOrEmpty(colValue7) ? 0 : Convert.ToDecimal(colValue7);
            t.UserID1 = Guid.NewGuid();
            t.InputDate1 = DateTime.Now;
            try
            {
                t.ID1 = Convert.ToInt32(colValue0);
                num = Convert.ToInt32(colValue0);
            }
            catch
            {
                LithologicSectionBLL.InsertLithologicSection(t);
            }

            if (colValue0 == Convert.ToString(t.ID1))
            {
               
                // LithologicSectionBLL.UpdateLithologicSection(t);
                MessageBox.Show("修改成功！");
            }
            gridControl1.DataSource = LithologicSectionBLL.GetLithologicSection().Tables[0];
            //SqlHelper.Open();
            //dispDt = SqlHelper.QuerySqlByDataTable("select * from tb_LithologicSection");
            //dispDt.Columns[0].ColumnName = "编号";
            //dispDt.Columns[1].ColumnName = "井号";
            //dispDt.Columns[2].ColumnName = "层位";
            //dispDt.Columns[3].ColumnName = "主要岩性";
            //dispDt.Columns[4].ColumnName = "底层井深(m)";
            //dispDt.Columns[5].ColumnName = "层厚(m)";
            //dispDt.Columns[6].ColumnName = "起始位置";
            //dispDt.Columns[7].ColumnName = "结束位置";
            //dispDt.Columns[8].ColumnName = "添加人";
            //dispDt.Columns[9].ColumnName = "添加时间";       

            //DataRow dr = dispDt.NewRow();
            //gridView1.BestFitColumns();

        }
    }
}